﻿Imports TSQLite
Imports TMySQL

Public Class TCaptureMyProcessList
    Implements System.IDisposable

    Public Enum TDatabaseType As Integer
        dtFile = 0
        dtInMemory = 1
    End Enum

    Private ReadOnly passwordChars As String = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Private _SQLiteConn As New TSQLiteConnection
    Private _SQLiteQuery As New TSQLiteQuery
    Private _SQLiteQuerySelect As New TSQLiteQuery
    Private Const DEFAULT_DATABASE_TYPE As TDatabaseType = TDatabaseType.dtInMemory
    Private Const CONFIG_FILE As String = "capture.conf"
    Private Const TMP_TABLE As String = "tmp_processlist"
    Private _MyProcessList As TMyProcessList
    Private Const PASSWORD_LENGTH As Integer = 16
    Private Const DATABASE_FILE_NAME As String = "processlist.db"
    Private _MySession As New TMySession
    Private _Captured As Boolean
    Private _CaptureSpan As Integer
    Private _DatabaseType As TDatabaseType

    Private Const DEFAULT_CAPTURE_SPAN As Integer = 1

    Private ReadOnly Property ConfigFile() As String
        Get
            Return Common.ApplicationPath & CONFIG_FILE
        End Get
    End Property

    Public ReadOnly Property DatabaseName() As String
        Get
            Select Case _DatabaseType
                Case TDatabaseType.dtFile
                    Return Common.ApplicationPath & DATABASE_FILE_NAME
                Case TDatabaseType.dtInMemory
                    Return ":memory:"
                Case Else
                    Return String.Empty
            End Select
        End Get
    End Property
    Public Property DatabaseType() As TDatabaseType
        Get
            Return _DatabaseType
        End Get
        Set(ByVal value As TDatabaseType)
            _DatabaseType = value
            WriteCaptureSpanToIniFile()
        End Set
    End Property

    Public Property CaptureSpan() As Integer
        Get
            Return _CaptureSpan
        End Get
        Set(ByVal value As Integer)
            If value <= 0 Then
                Throw New Exception("0より大きい値を設定してください")
            End If
            _CaptureSpan = value
            WriteCaptureSpanToIniFile()
        End Set
    End Property

    Private Sub WriteCaptureSpanToIniFile()
        Dim IniFile As New TIniFile(Me.ConfigFile)
        Try
            IniFile.WriteInteger("General", "Span", _CaptureSpan)
            IniFile.WriteInteger("General", "DatabaseType", _DatabaseType)
        Finally
            IniFile.Dispose()
        End Try
    End Sub

    Private Sub InitConfig()
        Dim IniFile As New TIniFile(Me.ConfigFile)
        Try
            _CaptureSpan = IniFile.ReadInteger("General", "Span", DEFAULT_CAPTURE_SPAN)
            _DatabaseType = DirectCast(IniFile.ReadInteger("General", "DatabaseType", DEFAULT_CAPTURE_SPAN), TDatabaseType)
        Finally
            IniFile.Dispose()
        End Try
    End Sub

#Region "コンストラクタ"
    Public Sub New(ByVal AMyConnection As TMySession)
        InitConfig()
        Dim pass As String = GetNewPassword()
        If _DatabaseType = TDatabaseType.dtFile Then
            If System.IO.File.Exists(Me.DatabaseName) Then
                System.IO.File.Delete(Me.DatabaseName)
            End If
        End If
        _Captured = False
        _MySession.UserName = AMyConnection.UserName
        _MySession.Password = AMyConnection.Password
        _MySession.DatabaseName = AMyConnection.DatabaseName
        _MySession.HostName = AMyConnection.HostName

        _SQLiteConn.FileName = Me.DatabaseName
        _SQLiteConn.Password = pass
        _SQLiteQuery.Connection = _SQLiteConn

        _SQLiteQuerySelect.Connection = _SQLiteConn
        InitSelectSQL()
    End Sub
#End Region

#Region "SQL取得"
    ''' <summary>
    ''' キャプチャ開始
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub StartCapture()
        If Not _MySession.Connected Then
            _MySession.Connected = True
        End If
        _MyProcessList = New TMyProcessList(_MySession, True)

        _SQLiteConn.Connected = True
        _SQLiteConn.ChangePassword()
        If Not TableExists Then
            CreateTable()
        End If
        InitInsertSQL()
        _Captured = True
    End Sub

    ''' <summary>
    ''' キャプチャ停止
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub StopCapture()
        _Captured = False
        If _MyProcessList IsNot Nothing Then
            _MyProcessList.Dispose()
        End If
        _SQLiteConn.Connected = False
    End Sub

    ''' <summary>
    ''' 履歴取得用テーブル存在確認
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property TableExists() As Boolean
        Get
            Try
                _SQLiteQuery.Clear()
                _SQLiteQuery.SQL.AppendLine("SELECT")
                _SQLiteQuery.SQL.AppendLine(" count(*) as cnt")
                _SQLiteQuery.SQL.AppendLine("from")
                _SQLiteQuery.SQL.AppendLine(" sqlite_master")
                _SQLiteQuery.SQL.AppendLine("where")
                _SQLiteQuery.SQL.AppendLine("type='table'")
                _SQLiteQuery.SQL.AppendLine("and name=@table_name")
                _SQLiteQuery.DeclareAndSet("table_name", TMP_TABLE)
                _SQLiteQuery.Execute()
                Return _SQLiteQuery.FieldAsInteger("cnt") > 0
            Finally
                _SQLiteQuery.Close()
            End Try
        End Get
    End Property

    ''' <summary>
    ''' 履歴取得用INSERT文の設定
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub InitInsertSQL()
        _SQLiteQuery.Clear()
        _SQLiteQuery.SQL.AppendLine("REPLACE INTO " & TMP_TABLE & "(")
        _SQLiteQuery.SQL.AppendLine(" datetime")
        _SQLiteQuery.SQL.AppendLine(",ID")
        _SQLiteQuery.SQL.AppendLine(",USER")
        _SQLiteQuery.SQL.AppendLine(",HOST")
        _SQLiteQuery.SQL.AppendLine(",DB")
        _SQLiteQuery.SQL.AppendLine(",COMMAND")
        _SQLiteQuery.SQL.AppendLine(",TIME")
        _SQLiteQuery.SQL.AppendLine(",STATE")
        _SQLiteQuery.SQL.AppendLine(",INFO")
        _SQLiteQuery.SQL.AppendLine(")VALUES(")
        _SQLiteQuery.SQL.AppendLine(" @datetime")
        _SQLiteQuery.SQL.AppendLine(",@ID")
        _SQLiteQuery.SQL.AppendLine(",@USER")
        _SQLiteQuery.SQL.AppendLine(",@HOST")
        _SQLiteQuery.SQL.AppendLine(",@DB")
        _SQLiteQuery.SQL.AppendLine(",@COMMAND")
        _SQLiteQuery.SQL.AppendLine(",@TIME")
        _SQLiteQuery.SQL.AppendLine(",@STATE")
        _SQLiteQuery.SQL.AppendLine(",@INFO")
        _SQLiteQuery.SQL.AppendLine(")")
        _SQLiteQuery.DeclareVariable("datetime")
        _SQLiteQuery.DeclareVariable("ID")
        _SQLiteQuery.DeclareVariable("USER")
        _SQLiteQuery.DeclareVariable("HOST")
        _SQLiteQuery.DeclareVariable("DB")
        _SQLiteQuery.DeclareVariable("COMMAND")
        _SQLiteQuery.DeclareVariable("TIME")
        _SQLiteQuery.DeclareVariable("STATE")
        _SQLiteQuery.DeclareVariable("INFO")

    End Sub

    ''' <summary>
    ''' 履歴取得用テーブル削除
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DropTable()
        If TableExists Then
            _SQLiteQuery.Clear()
            _SQLiteQuery.SQL.AppendLine("drop table " & TMP_TABLE)
            _SQLiteQuery.Execute()
        End If
    End Sub

    ''' <summary>
    ''' 履歴取得用テーブル作成
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CreateTable()
        _SQLiteQuery.Clear()
        _SQLiteQuery.SQL.AppendLine("create table " & TMP_TABLE & "(")
        _SQLiteQuery.SQL.AppendLine(" datetime text")
        _SQLiteQuery.SQL.AppendLine(",ID integer")
        _SQLiteQuery.SQL.AppendLine(",USER text")
        _SQLiteQuery.SQL.AppendLine(",HOST text")
        _SQLiteQuery.SQL.AppendLine(",DB text")
        _SQLiteQuery.SQL.AppendLine(",COMMAND text")
        _SQLiteQuery.SQL.AppendLine(",TIME integer")
        _SQLiteQuery.SQL.AppendLine(",STATE text")
        _SQLiteQuery.SQL.AppendLine(",INFO text")
        _SQLiteQuery.SQL.AppendLine(")")
        _SQLiteQuery.Execute()

        _SQLiteQuery.Clear()
        _SQLiteQuery.SQL.AppendLine("create unique index uk_" & TMP_TABLE & " on " & TMP_TABLE & "(info)")
        _SQLiteQuery.Execute()
        _SQLiteQuery.Clear()
        _SQLiteQuery.SQL.AppendLine("create index idx_" & TMP_TABLE & "_time on " & TMP_TABLE & "(time)")
        _SQLiteQuery.Execute()
    End Sub

    ''' <summary>
    ''' キャプチャの実行
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ExecuteCapture()
        If Not _Captured Then
            Return
        End If
        _MyProcessList.Refresh()
        Dim d As String = Now.ToString("yyyy/MM/dd HH:mm:ss")
        _SQLiteConn.BeginTransaction()
        For i As Integer = 0 To _MyProcessList.Count - 1
            _SQLiteQuery.SetVariable("datetime", d)
            _SQLiteQuery.SetVariable("ID", _MyProcessList(i).Id)
            _SQLiteQuery.SetVariable("USER", _MyProcessList(i).User)
            _SQLiteQuery.SetVariable("HOST", _MyProcessList(i).Host)
            _SQLiteQuery.SetVariable("DB", _MyProcessList(i).DB)
            _SQLiteQuery.SetVariable("COMMAND", _MyProcessList(i).Command)
            _SQLiteQuery.SetVariable("TIME", _MyProcessList(i).Time)
            _SQLiteQuery.SetVariable("STATE", _MyProcessList(i).State)
            _SQLiteQuery.SetVariable("INFO", _MyProcessList(i).Info)

            _SQLiteQuery.Execute()
        Next
        _SQLiteConn.Commit()
    End Sub

    ''' <summary>
    ''' キャプチャ中 or Not
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property Captured() As Boolean
        Get
            Return _Captured
        End Get
        Set(ByVal value As Boolean)
            If value Then
                StartCapture()
            Else
                StopCapture()
            End If
        End Set
    End Property


#End Region

#Region "取得結果の取得"
    Public Sub GetHistory()
        _SQLiteQuerySelect.Close()
        _SQLiteQuerySelect.Execute()
    End Sub

    ''' <summary>
    ''' DataTable取得
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property DataTable() As DataTable
        Get
            Return _SQLiteQuerySelect.DataSet.Tables(0).Copy
        End Get
    End Property

    Private Sub InitSelectSQL()
        _SQLiteQuerySelect.Clear()
        _SQLiteQuerySelect.SQL.AppendLine("select")
        _SQLiteQuerySelect.SQL.AppendLine(" datetime")
        _SQLiteQuerySelect.SQL.AppendLine(",ID")
        _SQLiteQuerySelect.SQL.AppendLine(",USER")
        _SQLiteQuerySelect.SQL.AppendLine(",HOST")
        _SQLiteQuerySelect.SQL.AppendLine(",DB")
        _SQLiteQuerySelect.SQL.AppendLine(",COMMAND")
        _SQLiteQuerySelect.SQL.AppendLine(",TIME")
        _SQLiteQuerySelect.SQL.AppendLine(",STATE")
        _SQLiteQuerySelect.SQL.AppendLine(",INFO")
        _SQLiteQuerySelect.SQL.AppendLine("from")
        _SQLiteQuerySelect.SQL.AppendLine(" " & TMP_TABLE)
        _SQLiteQuerySelect.SQL.AppendLine("order by")
        _SQLiteQuerySelect.SQL.AppendLine(" TIME desc")
    End Sub

#End Region

    'パスワードに使用する文字

    ''' <summary>
    ''' ランダムな文字列を生成する
    ''' </summary>
    ''' <param name="length">生成する文字列の長さ</param>
    ''' <returns>生成された文字列</returns>
    Private Function GeneratePassword(ByVal length As Integer) As String
        Dim sb As New System.Text.StringBuilder(length)
        Dim r As New Random()

        For i As Integer = 0 To length - 1
            Dim pos As Integer = r.Next(passwordChars.Length)
            Dim c As Char = passwordChars(pos)
            sb.Append(c)
        Next
        Return sb.ToString()
    End Function

    Private Function GetNewPassword() As String
        Return GeneratePassword(PASSWORD_LENGTH)
    End Function

    Private disposedValue As Boolean = False        ' 重複する呼び出しを検出するには

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If
            If _MyProcessList IsNot Nothing Then
                _MyProcessList.Dispose()
            End If
            If _MySession.Connected Then
                _MySession.Connected = False
            End If
            _MySession.Dispose()
            _SQLiteConn.Connected = False
            If System.IO.File.Exists(_SQLiteConn.FileName) Then
                System.IO.File.Delete(_SQLiteConn.FileName)
            End If
            _SQLiteConn.Dispose()
            ' TODO: ユーザー独自の状態を解放します (アンマネージ オブジェクト)。
            ' TODO: 大きなフィールドを null に設定します。
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
